/**
 * 
 */
package com.placement.app.dao;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;
import java.util.Map;

import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.springframework.jdbc.core.JdbcTemplate;

import com.placement.app.model.JobData;
import com.placement.app.model.ZipLocation;

/**
 * @author ns_nagar
 *
 */
public class JobSearchDAOImpl extends JdbcTemplate implements JobSearchDAO {
	
	private static final Log logger = LogFactory.getLog(JobSearchDAOImpl.class);
	/* (non-Javadoc)
	 * @see com.placement.app.dao.JobSearchDAO#getJobList(com.placement.app.model.ZipLocation, int)
	 */
	public ArrayList<JobData> getJobList(String searchString, ZipLocation location, int range) {
		ArrayList<JobData> jobs = new ArrayList<JobData>();
		String sql = "";
		if (location != null && location.getZipcode()!= null && location.getZipcode() != 0)
			sql = getSQLForZipcode(searchString, location, range);
		else if(location.getState() != null && !"".equals(location.getState()))
			sql = getSQLForSate(searchString, location);
		else 
			sql = getSQLForUS(searchString, location);
		List list = queryForList(sql);
		Long zipcode = new Long(0L);
		Iterator itr = list.iterator();
		if (itr.hasNext()) {
			JobData job = new JobData();
			while (itr.hasNext()) {
				Map map = (Map) itr.next();
				job = new JobData();
				if (map.get("JOB_ID") != null) {
					job.setJobId((Long)map.get("JOB_ID"));
				}
				if (map.get("SOURCE_ID") != null) {
					job.setSourceId((Long)map.get("SOURCE_ID"));
				}
				job.setCategory((String)map.get("JOB_CATEGORY"));
				job.setDescription((String)map.get("DESCRIPTION"));
				if (map.get("COMPANY") != null) {
					job.setCompanyId((Long)map.get("COMPANY"));
				}
				job.setCountry((String)map.get("COUNTRY"));
				job.setState((String)map.get("STATE"));
				job.setCity((String)map.get("CITY"));
				job.setLocation((String)map.get("LOCATION"));
				job.setZipcode((String)map.get("ZIP_CODE"));
				job.setSalary((String)map.get("SALARY"));
				job.setUrl((String)map.get("URL"));
				job.setExperience((String)map.get("EXPERIENCE"));
				job.setTitle((String)map.get("TITLE"));
				if (map.get("WEIGHT") != null) {
					job.setWeight((Integer) map.get("WEIGHT"));
				}
				jobs.add(job);
			}
	
			return jobs;
		}
		return null;
	}
	private String getSQLForUS(String searchString, ZipLocation location) {
		// This method get all jobs for searchString
		//String state = location.getState();
		//if(state != null && !"".equals(state)){
			String sql = "select * from jobs j " + //, zipcodes z " +
			"WHERE MATCH (title,description) " +
			"against ('"+searchString+"' IN NATURAL LANGUAGE MODE)";
			//"where (upper(title) like upper('%"+searchString+"%') " +
			//"or upper(description) like upper('%"+searchString+"%'))";
			logger.info(">>loadZipcodes>> SQL:" + sql);
			return sql;
		//}
		//return null;
	}
	private String getSQLForSate(String searchString, ZipLocation location) {
		// This method looks at the state information only
		String state = location.getState();
		if(state != null && !"".equals(state)){
			String sql = "select * from jobs j, zipcodes z " +
			"where convert(j.zip_code, UNSIGNED) = z.zipcode " +		
			"and z.state like '"+ state + "' " + 
			" and MATCH (title,description) " +
			"against ('"+searchString+"' IN NATURAL LANGUAGE MODE)";
			logger.info(">>loadZipcodes>> SQL:" + sql);
			return sql;
		}
		return null;
	}
	private String getSQLForZipcode(String searchString, ZipLocation location,
			int range) {
		if(range<=5)
			range = 5;
		double lon = (location==null)?0d:location.getLon();
		double lat = (location==null)?0d:location.getLat();
		double latRange = range/69.172;
		double lonRange = Math.abs(range/(Math.acos(lat/57.3) * 69.172));
		
		double minLat   = (lat==0d)?17:lat - latRange;
		double maxLat   = (lat==0d)?72:lat + latRange;
		double minLon   = (lon==0d)?-180:lon - lonRange;
		double maxLon   = (lon==0d)?-70:lon + lonRange;


		String sql = "select * from jobs j, zipcodes z " +
					"where convert(j.zip_code, UNSIGNED) = z.zipcode " +		
					"and longitude between "+ minLon + " and " + maxLon + 
					" and latitude between " +minLat+ " and " +maxLat+ 
					" and MATCH (title,description) " +
					"against ('"+searchString+"' IN NATURAL LANGUAGE MODE)";
		logger.info(">>loadZipcodes>> SQL:" + sql);
		return sql;
	}

}
